package com.ncp.model.biz.comment;
/**
 * 对应评论的处理
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ncp.model.dao.DB;
import com.ncp.model.dao.DbConnection;
import com.ncp.model.entity.comment.ProductComment;
import com.ncp.model.entity.registerlogin.UserBasicBean;

public class CommentCl {
	private Connection conn=null;
	private PreparedStatement ps=null;
	private ResultSet rs=null;
	private int pageCount=0;
	private int rowCount=0;
	private int pageSize=1;
	
	/**
	 * 保存用户评论
	 * @param ProductID产品ID号
	 * @param username用户名
	 * @param IsDisplayUserName是否匿名--匿名(0),不匿名(1)
	 * @param comment评论内容
	 * @param score评价分数
	 * @return 存储成功或失败
	 */
	public boolean saveComment(int ProductID,String username,byte IsDisplayUserName,String comment,int score){
		int UserID=1;
		boolean b=false;
		conn=DbConnection.getConnByDataSource();
		try {
			ps=conn.prepareStatement("select UserID from USERBASIC where UserLoginName='"+username+"'");
			rs=ps.executeQuery();
			if(rs.next()){
				UserID=rs.getInt(1);
			}
			String sql="insert into PRODUCTCOMMENT (ProductID,UserID,IsDisplayUserName,Comment,Score,CommentTime) " +
			"value ("+ProductID+",'"+UserID+"',"+IsDisplayUserName+",'"+comment+"',"+score+",now())";
			ps=conn.prepareStatement(sql);
			int i=ps.executeUpdate();
			if(i!=0){
				b=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			new DB().closeSQL(conn, ps);
		}
		
		return b;
	}
	
	
	/**
	 * 得到评论信息
	 * @param ProductID产品ID
	 * @return 该产品的所有评论信息
	 */
	public ArrayList<ProductComment> getCommentInfo(int ProductID,int nowPage){
		ArrayList<ProductComment> list=new ArrayList<ProductComment>();
		String sql="select * from PRODUCTCOMMENT where ProductID="+ProductID+" and Comment<>'' order by CommentTime desc limit "+pageSize*(nowPage-1)+","+pageSize+"";
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				ProductComment pc=new ProductComment();
				pc.setCommentID(rs.getInt("CommentID"));
				pc.setUserID(rs.getInt("UserID"));
				pc.setIsDisplayUserName(rs.getByte("IsDisplayUserName"));
				pc.setComment(rs.getString("Comment"));
				pc.setScore(rs.getInt("Score"));
				pc.setCommentTime(rs.getTimestamp("CommentTime"));
				list.add(pc);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			new DB().closeSQL(conn, ps);
		}
		return list;
	}
	
	/**
	 * 的到评论的数量
	 * @return
	 */
	public int  getCommentNum(int ProductID){
		int count=0;
		String sql="select count(*) from  PRODUCTCOMMENT where Comment<>'' and ProductID="+ProductID;
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			new DB().closeSQL(conn, ps);
		}
		return count;
	}
	
	/**
	 * 删除评论
	 * @param id评论的ID
	 * @return是否删除成功
	 */
	public boolean deleteComment(int id){
		boolean b=false;
		String sql="delete from PRODUCTCOMMENT where CommentID="+id;
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement(sql);
			int r=ps.executeUpdate();
			if(r!=0){
				b=true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			new DB().closeSQL(conn, ps);
		}
		return b;
	}
	
	/**
	 * 修改评论内容
	 * @param id评论ID
	 * @param comment评论内容
	 * @return
	 */
	public boolean updateComment(int id,String comment){
		boolean b=false;
		String sql="update PRODUCTCOMMENT set Comment='"+comment+"' where CommentID="+id+"";
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement(sql);
			int r=ps.executeUpdate();
			if(r!=0){
				b=true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			new DB().closeSQL(conn, ps);
		}
		return b;
	}
	
	/**
	 * 得到某个产品评论的总页数
	 * @param ProductID产品的ID
	 * @return
	 */
	public int getCount(int ProductID){
		
		rowCount=this.getCommentNum(ProductID);
		if(rowCount%pageSize==0){
			pageCount=rowCount/pageSize;
		}else if(rowCount%pageSize!=0){
			pageCount=rowCount/pageSize+1;
		}
		return pageCount;
	}
	
	/**
	 * 得到每条评价的用户名
	 * @param id用户ID
	 * @return用户名
	 */
	public String getUsername(int id){
		String name="";
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement("select UserLoginName from USERBASIC where UserID="+id);
			rs=ps.executeQuery();
			if(rs.next()){
				name=rs.getString(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			new DB().closeSQL(conn, ps);
		}
		return name;
	}
	
	/**
	 * 取出用户的登录信息
	 * @param name
	 * @return
	 */
	public UserBasicBean getUserInfo(String name){
		UserBasicBean user=new UserBasicBean();
		try {
			conn=DbConnection.getConnByDataSource();
			ps=conn.prepareStatement("select * from USERBASIC where UserLoginName='"+name+"'");
			rs=ps.executeQuery();
			if(rs.next()){
				name=rs.getString(1);
				user.setLevelID(rs.getInt("LevelID"));
				user.setUserID(rs.getInt("UserID"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			try{
				if(rs!=null)
					rs.close();
			}catch(Exception e){
				e.printStackTrace();
			}
			new DB().closeSQL(conn, ps);
		}
		return user;
	}
	
	
	
	
	

}
